Access plan for a database query

ABSTRACT

A system and method of creating an access plan for a database query is disclosed. The system and method include identifying a first portion of a column of a table of a database. The first portion of the column may be in a select statement of the database query. The system and method include estimating a first statistical value for the column. The estimating may occur by analyzing a second portion of the column. The system and method include generating the access plan to predict a characteristic of a set of results for the database query. In generating the access plan, the first statistical value for the column may be used.

TECHNICAL FIELD

This disclosure relates generally to database management systems and,more particularly, relates to database queries.

BACKGROUND

Databases are used to store information for numerous types ofapplications. Examples include various industrial, commercial,technical, scientific, and educational applications. Database managementsystems (DBMSs) are a typical mechanism for accessing data stored in adatabase. DBMSs are typically configured to separate the process ofstoring data from accessing, manipulating, or using data stored in adatabase. DBMSs often require tremendous resources to handle the heavyworkloads placed on such systems. As such, it may be useful to increasethe performance of database management systems with respect toprocessing searches, or queries, to databases.

SUMMARY

Aspects of the disclosure include a system and method of creating anaccess plan for a database query. The system and method includeidentifying a first portion of a column of a table of a database. Thefirst portion of the column may be in a select statement of the databasequery. The system and method include estimating a first statisticalvalue for the column. The estimating may occur by analyzing a secondportion of the column. The system and method include generating theaccess plan to predict a characteristic of a set of results for thedatabase query. In generating the access plan, the first statisticalvalue for the column may be used.

Aspects of the disclosure may reduce CPU consumption and may promoteefficient query performance. The first statistical value for the columnmay include cardinality for the column (e.g., number of unique values inthe column). In embodiments, selectivity may be the first statisticalvalue for the column. Selectivity (e.g., ratio of distinct values to thenumber of entries) may be the characteristic of the set of results forthe database query. The characteristic of the set of results for thedatabase query may include a number of rows.

Aspects of the disclosure include using an analytics engine to estimate,by analyzing the second portion of the column, the first statisticalvalue for the column. Data from the table of the database may betransmitted to the analytics engine. A specific query related to datafrom the table of the database may be transmitted to the analyticsengine. The specific query may be resolved by the analytics engine.Resolving the specific query may include comparing a corpus and datafrom the table of the database. The first statistical value for thecolumn may be determined by the analytics engine in response toresolving the specific query.

BRIEF DESCRIPTION OF THE DRAWINGS

FIG. 1 illustrates an example representation of a computer systemconnected to a client computer via a network according to an embodiment;

FIG. 2 illustrates an example database management system (DBMS)according to an embodiment;

FIG. 3 is a flowchart illustrating a method to create an access plan fora database query according to embodiments;

FIG. 4 shows modules of a system for creating an access plan for adatabase query according to embodiments; and

FIG. 5 is an example system for creating an access plan for a databasequery according to embodiments.

DETAILED DESCRIPTION

Aspects of the disclosure leverage data analytics to provide certainfeatures/answers about a data set previously available principallythrough use of statistics. Usable statistics can take a significantamount of time and resources to collect and may not be available for useas soon as requested. A data analytics approach may reduce maintenanceand decrease reliance on specific data values in the data set at thetime of a query. When statistics are unavailable or costly, at least aportion of the data set can be analyzed to classify data and estimatecharacteristics of data.

Development efforts have been directed toward query “optimization,”whereby the execution of particular searches, or queries, is optimizedin an automated manner to minimize the amount of resources required toexecute each query. A query optimizer typically generates, for eachsubmitted query, an access plan. In generating an access plan, a queryoptimizer typically creates multiple potential access plans, and selectsthe best among those potential access plans based at least in part uponthe “cost” of each plan. Querying a large amount of data may take anamount of time and performance overhead that is not insignificant. Thecost of each plan can represent the amount of resources expected to beutilized by the plan (typically expressed in the expected amount of timeto execute), and as such, selecting the plan with the lowest costtypically results in the most efficient (and quickest) execution of aquery. When statistics are not available or useful, the query optimizermay make assumptions or simply produce a guess in generating aparticular access plan. Using data analytics may have a positive impactrelative to such assumptions or guesses.

Aspects of the disclosure include a system and method of creating anaccess plan for a database query. The system and method includeidentifying a first portion of a column of a table of a database. Thefirst portion of the column may be in a select statement of the databasequery. The system and method include estimating a first statisticalvalue for the column. The estimating may occur by analyzing a secondportion of the column. The system and method include generating theaccess plan to predict a characteristic of a set of results for thedatabase query. In generating the access plan, the first statisticalvalue for the column may be used.

Aspects of the disclosure may include the first statistical value forthe column including cardinality for the column (e.g., number of uniquevalues in the column). In embodiments, selectivity may be the firststatistical value for the column. Selectivity (e.g., ratio of distinctvalues to the number of entries) may be the characteristic of the set ofresults for the database query. The characteristic of the set of resultsfor the database query may include a number of rows. In embodiments, thedatabase query may include a join. The characteristic of the set ofresults for the database query may include an expected join countrelated to the number of rows.

In embodiments, analyzing the second portion of the column may includecomparing the second portion of the column to profile data. Inembodiments, a column header may be at least a part of the secondportion of the column. In embodiments, analyzing the second portion ofthe column may include determining a measure of correlation of thecolumn with another column of the table of the database that ispersistently (or permanently) related to the column (e.g., states andzip codes). In embodiments, analyzing the second portion of the columnmay include classifying the second portion of the column into acategory. Aspects of the disclosure include using an analytics engine toestimate, by analyzing the second portion of the column, the firststatistical value for the column. Data from the table of the databasemay be transmitted to the analytics engine. A specific query related todata from the table of the database may be transmitted to the analyticsengine (e.g., for natural language processing). The specific query maybe resolved by the analytics engine (e.g., using natural languageprocessing). Resolving the specific query may include comparing a corpus(e.g., data store) and data from the table of the database. The firststatistical value for the column may be determined by the analyticsengine in response to resolving the specific query.

Aspects of the disclosure may include determining a statistics table isunsuitable for creating the access plan for the database query. Thestatistics table may be unsuitable in response to at least one of one ofa group selected from an absence of the statistics table (e.g., nostatistics table has been built), an absence of records in thestatistics table (e.g., a statistics table has been built but thestatistics table contains no records), reaching a threshold burden ofcomputing resources used for accessing the statistics table (e.g., usingcomputing resources to access the statistics table is not feasible in acomputing environment), or a user choice to not utilize the statisticstable (e.g., a database administrator makes a professional judgment orbusiness decision such as deeming information in the statistics table tobe insufficient or unreliable such as being out of date and too old).Aspects of the disclosure may reduce CPU consumption. Aspects of thedisclosure may promote efficient query performance.

FIG. 1 illustrates an example representation of a computer system 100connected to one or more client computers 160 via a network 155,according to some embodiments. For the purposes of this disclosure,computer system 100 may represent practically any type of computer,computer system, or other programmable electronic device, including butnot limited to, a client computer, a server computer, a portablecomputer, a handheld computer, an embedded controller, etc. In someembodiments, computer system 100 may be implemented using one or morenetworked computers, e.g., in a cluster or other distributed computingsystem.

The computer system 100 may include, without limitation, one or moreprocessors (CPUs) 105, a network interface 115, an interconnect 120, amemory 125, and a storage 130. The computer system 100 may also includean I/O device interface 110 used to connect I/O devices 112, e.g.,keyboard, display, and mouse devices, to the computer system 100.

Each processor 105 may retrieve and execute programming instructionsstored in the memory 125 or storage 130. Similarly, the processor 105may store and retrieve application data residing in the memory 125. Theinterconnect 120 may transmit programming instructions and applicationdata between each processor 105, I/O device interface 110, networkinterface 115, memory 125, and storage 130. The interconnect 120 may beone or more busses. The processor 105 may be a single central processingunit (CPU), multiple CPUs, or a single CPU having multiple processingcores in various embodiments. In one embodiment, a processor 105 may bea digital signal processor (DSP).

The memory 125 may be representative of a random access memory, e.g.,Static Random Access Memory (SRAM), Dynamic Random Access Memory (DRAM),read-only memory, or flash memory. The storage 130 may be representativeof a non-volatile memory, such as a hard disk drive, solid state device(SSD), or removable memory cards, optical storage, flash memory devices,network attached storage (NAS), or connections to storage area network(SAN) devices, or other devices that may store non-volatile data. Thenetwork interface 115 may be configured to transmit data via thecommunications network 155.

The memory 125 may include a database management system (DBMS) 135, aresult set 140, a query 145, and applications 150. Although theseelements are illustrated as residing in the memory 125, any of theelements, or combinations thereof, may reside in the storage 130 orpartially in the memory 125 and partially in the storage 130. Each ofthese elements will be described in greater detail in accordance withFIG. 2.

The network 155 may be any suitable network or combination of networksand may support any appropriate protocol suitable for communication ofdata and/or code to/from the server computer system 100 and the clientcomputer system 160. In some embodiments, the network 155 may supportwireless communications. In other embodiments, the network 155 maysupport hardwired communications. The network 155 may be the Internetand may support Internet Protocol in some embodiments. In otherembodiments, the network 155 may be implemented as a local area network(LAN) or a wide area network (WAN). The network 155 may also beimplemented as a cellular data network. Although the network 155 isshown as a single network in the figures, one or more networks of thesame or different types may be included.

The client computer system 160 may include some or all of the hardwareand software elements of the computer system 100 previously described.As shown, there may be one or more client computers 160 connected to thecomputer system 100 via the network 155. In some embodiments, one ormore client computers 160 may send a query 145 by network 155 tocomputer system 100 and receive a result set 140.

FIG. 2 illustrates an example database management system (DBMS) 135. TheDBMS 135 may include a parser 210, an optimizer 220, an execution engine230, and a database 232. The parser 210 may receive a database query 145from an application 150. In some embodiments, the database query 145 maybe in the form of a Structured Query Language (SQL) statement. Theparser 210 may generate a parsed statement 215. The parser 210 may sendthe parsed statement 215 to an optimizer 220. The optimizer 220 mayattempt to optimize the parsed statement. In some embodiments,optimizing may improve the performance of the database query 145 by, forexample, reducing the amount of time it takes to provide a user with aresponse. The optimizer 220 may generate an execution plan 246 (accessplan), which may be maintained in a query plan cache 245, according tosome embodiments. The query plan cache 245 may include one or moreexecution plans 246, including the current execution plan as well aspreviously used execution plans. Once an execution plan 246 isgenerated, the execution plan 246 may be sent to the execution engine230. The execution engine 230 may execute the query 145. Executing thequery 145 may include finding and retrieving data in the database tables235 that satisfies the criteria supplied in the query 145. The executionengine 230 may store the data returned matching the query 145 in aresult set 140. The DBMS 135 may return the result set 140 to anapplication 150, such as the application in which the database query 145was generated, as a response to the database query 145.

A database 232 may include one or more tables 235 and, in someembodiments, one or more indexes 240. A database table 235 may organizedata into rows and columns. Each row of a database table 235 maycorrespond to an individual entry, a tuple, or a record in the database232. A column may define what is stored in each entry, tuple, or record.In some embodiments, columns of a table 235 may also be referred to asfields or attributes. Each table 235 within the database 232 may have aunique name. Each column within a table 235 may also have a unique name.A row, tuple, or record, however, within a particular table 235 may notbe unique, according to some embodiments. A database 232 may alsoinclude one or more indexes 240. An index 240 may be a data structurethat may inform the DBMS 135 of the location of a particular recordwithin a table 235 if given a particular indexed column value. In someembodiments, the execution engine 230 may use the one or more indexes240 to locate data within a table 235. In other embodiments, theexecution engine 230 may scan the tables 235 without using an index 240.

To facilitate the optimization of queries, DBMS 135 may also include astatistics manager. The statistics manager may be used to gather andanalyze statistical information for the optimizer 220. The statisticalinformation may be derived by the statistics manager from by usingand/or creating indexes and column statistics. These column statisticsmay be merged during optimization to estimate a join fanout. Thestatistics manager may also identify join networks and detect skewvalues. To assist in generating an optimal access plan, query optimizer220 can be configured to communicate with and receive communication fromstatistics manager. It will be appreciated, however, that the queryoptimizer and statistics manager may be allocated differentfunctionality in embodiments. Moreover, the statistics manager may becontained or integrated within the query optimizer. The statisticsmanager may also have other components or may be omitted completely insome embodiments. Furthermore, other components and functionality mayalso be incorporated into DBMS 135, as may other suitable databasemanagement architectures. Other database programming and organizationalarchitectures may also be used consistent with embodiments.

FIG. 3 is a flowchart illustrating a method 300 to create an access planfor a database query according to embodiments. The method 300 begins atblock 301. In embodiments, the method may include determining astatistics table is unsuitable for creating the access plan for thedatabase query at block 305. The statistics table may be unsuitable inresponse to an absence of the statistics table (e.g., no statisticstable has been built). For example, general system performance concernsled to disablement of building the statistics table. The statisticstable may be unsuitable in response to an absence of records in thestatistics table (e.g., a statistics table has been built but thestatistics table contains no records). For example, table data is newlyfresh and maintenance operations have yet to populate the statisticstable but query capability is requested. The statistics table may beunsuitable in response to reaching a threshold burden of computingresources used for accessing the statistics table (e.g., using computingresources to access the statistics table is not feasible in a computingenvironment). For example, during peak hours energy consumption is at apremium and operations related to statistics tables may occur (only)during off-peak hours. The statistics table may be unsuitable inresponse to a user choice to not utilize the statistics table (e.g., adatabase administrator makes a professional judgment or businessdecision such as deeming information in the statistics table to beinsufficient or unreliable). For example, the database administratorbelieves data in the statistics table may be distorted due to, forinstance, periodic downtime or lag-time of certain servers.

At block 310, the method includes identifying a first portion of acolumn of a table of a database. The first portion of the column mayinclude a column header. The first portion of the column may include afield of a row (and thus may include fields of many rows). The firstportion of the column may be in a select statement of the databasequery. For example, the select statement may be: “wherecolumn2header=valueoffieldofcolumn2row3.” As a more particular example,the select statement may be: “where State=MT” (put differently, wherethe U.S. State is Montana).

At block 320, the method includes estimating a first statistical valuefor the column. The first statistical value may be a parameter (i.e., anumerical or other measurable factor forming one of a set that defines asystem or sets the conditions of its operation). The first statisticalvalue for the column may include cardinality for the column. Cardinalitymay include the number of distinct values (e.g., different/uniquevalues). For example a table with a column State in the United Stateshas 50 possible values and thus cardinality of that column is 50.Cardinality may be considered high (very uncommon), normal (somewhatuncommon or a variety of possibilities), or low (mostly the samevalues). In embodiments, selectivity may be the first statistical valuefor the column. Selectivity may be calculated using cardinality (e.g.,as at least part of the computation). Selectivity may include alikelihood of finding a match (e.g., variation) or how many differentvalues are available in a given sample set. For instance, determiningselectivity may assist the query optimizer in deciding whether to use afirst index to find certain rows in a table, to use a second index, touse a third index, or to simply use the table.

The estimating may occur by analyzing a second portion of the column.The second portion of the column may include a column header (putdifferently, the column header may be at least a part of the secondportion of the column). The second portion of the column may include afield of a row (and thus may include fields of many rows). It ispossible that the first and second portions of the column are identical,though in many instances they may be at least slightly different. Forexample, the second portion of the column could include the columnheader “State” and also field values “MN,” “NM,” “NY,” “OH,” and “OK.”Different permutations or combinations may lead to distinct analyses.For example, only analyzing the column header “State” could refer to astate of being, a physical state (e.g., solid, liquid, gas), or manyother states. Only analyzing the fields “OH” and “OK” could refer to acolumn of two letter words. Only analyzing “MN” and “NM” could refer toconsecutive letters in the alphabet. Only analyzing “NY” could refer toshorthand for not yet. Only analyzing the column header “State” and thefield “OK” could refer to the state of feelings of a person. Distinctcardinalities and selectivities may result from each of these scenarios.A data analytics approach may provide efficiencies in estimating thefirst statistical value for the column.

In embodiments, analyzing the second portion of the column may includecomparing the second portion of the column to profile data at block 321.For example, comparing field values “MN,” “NM,” “NY,” “OH,” and “OK”with profile data include sets of known abbreviations or sets of dataknown to be collected for certain tables (e.g., identifications). Inembodiments, analyzing the second portion of the column may includedetermining a measure of correlation of the column with another columnof the table of the database that is persistently related to the column.For example, the column having state information may highly correlate toanother column having zip codes. Such columns may be persistently oreven permanently related. Ranges of correlation may occur, and dataanalytics may recognize an amount of correlation to provide moreefficient estimates for statistical values. In embodiments, analyzingthe second portion of the column may include classifying the secondportion of the column into a category. For example, if another columnincludes “49401,” “55901,” and “87123,” then it may be classified ashaving a zip code category.

Aspects of the disclosure include using an analytics engine at block 322to estimate, by analyzing the second portion of the column, the firststatistical value for the column. Data from the table of the databasemay be transmitted to the analytics engine. A specific query related todata from the table of the database may be transmitted to the analyticsengine (e.g., for natural language processing which may identify one ormore concepts associated with the different elements in the specificquery and use these concepts to filter though the different data sourcesstored in a corpus). The specific query may be resolved by the analyticsengine (e.g., using natural language processing which may evaluate thespecific query to identify a data source that may contain an answer tothe specific query). Resolving the specific query may include comparingthe corpus (e.g., data store of journals, websites, magazines, referencebooks, textbooks that may have been ingested) and data from the table ofthe database. The first statistical value for the column may bedetermined by the analytics engine in response to resolving the specificquery. For example, “MN,” “NM,” “NY,” “OH,” and “OK” may be resolved asstate abbreviations and the first statistical value may be cardinalitywhich would be 50. Without the analytics engine, permutations orcombinations of two letters may have led to a much different firststatistical value. The effect of the much different first statisticalvalue could impede efficiency and the use of computing resources inperforming the database query.

At block 330, the method includes generating the access plan to predicta characteristic of a set of results for the database query. Ingenerating the access plan, the first statistical value for the columnmay be used. In embodiments, cardinality may be used. Cardinality may beused as at least a component in determining selectivity. In embodiments,selectivity may be the characteristic of the set of results for thedatabase query. The characteristic of the set of results for thedatabase query may include a number of rows. In embodiments, thedatabase query may include a join. The characteristic of the set ofresults for the database query may include an expected join countrelated to the number of rows.

Consider the following example. Every day, a database administratorbuilds a new set of tables, based on data in other “permanent” tables.The database administrator then use this new set of tables to run a setof queries. Since the database administrator builds a new copy of theset of tables every day, the database administrator does not want tobuild indexes (because that takes a significant amount of time). Withoutmethod 300, the database administrator has a challenge with a two-wayjoin between two tables of the new set of table—a first table which isvery large and a second table which is very small. Since both tables arenewly fresh, statistics have not been collected in a statistics table.Without method 300, a default cardinality for the join column is issuedand an expected join record count of roughly 100 million rows ispredicted. The optimizer ultimately chooses to put the smaller secondtable first in the join order, followed by the larger second table undera hash probe. Under these conditions, the query runs in approximately 3minutes (180 seconds). However, using data analytics of method 300, thequery may be run a second time under like conditions. Cardinality of 14million may be arrived at for the first statistical value. With thiscardinality, an expected join count of only 27 rows is predicted. Inactuality, the real join count is 8. Therefore, the data analyticsapproach may produce a better estimate. With the better estimate, theoptimizer chooses a plan placing the larger first table first uses lookahead predicate generation. With this plan using method 300, the queryruns in approximately 3 seconds (or approximately 6000% more efficientthan not using method 300).

Method 300 may conclude at block 399. Method 300 may reduce CPUconsumption. Method 300 may be used when statistics are unavailable orcostly. Method 300 may be used when at least a portion of the data setcan be analyzed to classify data and estimate characteristics of data.Method 300 may promote efficient query performance.

FIG. 4 shows modules of a system for creating an access plan for adatabase query according to embodiments. In embodiments, aspects ofmethod 300 may be consistent with and/or implemented using one or moremodules of FIG. 4 (particular aspects may be similar or the same). Themodules may be implemented in hardware, software or firmware executableon hardware, or a combination thereof. For example, module functionalitythat may occur in a query optimizer 496 may actually be implemented inan analytics engine 490 and vice versa. Other functionality may bedistributed across the query optimizer 496 and the analytics engine 490.

The query optimizer 496 may include an access plan module 400. Theaccess plan module 400 may be configured and arranged to create theaccess plan for the database query. The access plan module 400 mayinclude an identifying module 410, an estimating module 420, and agenerating module 430. The access plan module 400 may further include asuitability module 405, a comparing module 421, and an interfacingmodule 422. The analytics engine 490 may have a corpus 440 comprisingprofile data 441 having data sources 442.

In embodiments, the access plan module 400 may include determining astatistics table is unsuitable for creating the access plan for thedatabase query by the suitability module 405. The statistics table maybe unsuitable in response to an absence of the statistics table (e.g.,no statistics table has been built). The statistics table may beunsuitable in response to an absence of records in the statistics table(e.g., a statistics table has been built but the statistics tablecontains no records). The statistics table may be unsuitable in responseto reaching a threshold burden of computing resources used for accessingthe statistics table (e.g., using computing resources to access thestatistics table is not feasible in a computing environment). Thestatistics table may be unsuitable in response to a user choice to notutilize the statistics table (e.g., a database administrator makes aprofessional judgment or business decision such as deeming informationin the statistics table to be insufficient or unreliable).

The identifying module 410 includes identifying a first portion of acolumn of a table of a database. The first portion of the column mayinclude a column header. The first portion of the column may include afield of a row (and thus may include fields of many rows). The firstportion of the column may be in a select statement of the databasequery.

The estimating module 420 includes estimating a first statistical valuefor the column. The first statistical value may be a parameter (i.e., anumerical or other measurable factor forming one of a set that defines asystem or sets the conditions of its operation). The first statisticalvalue for the column may include cardinality for the column. Cardinalitymay include the number of distinct values (e.g., different/uniquevalues). Cardinality may be considered high (very uncommon), normal(somewhat uncommon or a variety of possibilities), or low (mostly thesame values). In embodiments, selectivity may be the first statisticalvalue for the column. Selectivity may be calculated using cardinality(e.g., as at least part of the computation). Selectivity may include alikelihood of finding a match (e.g., variation) or how many differentvalues are available in a given sample set. For instance, determiningselectivity may assist the query optimizer in deciding whether to use afirst index to find certain rows in a table, to use a second index, touse a third index, or to simply use the table.

The estimating by the estimating module 420 may occur by analyzing asecond portion of the column. The second portion of the column mayinclude a column header (put differently, the column header may be atleast a part of the second portion of the column). The second portion ofthe column may include a field of a row (and thus may include fields ofmany rows). It is possible that the first and second portions of thecolumn are identical, though in many instances they may be at leastslightly different. Different permutations or combinations may lead todistinct analyses. A data analytics approach may provide efficiencies inestimating the first statistical value for the column.

In embodiments, analyzing the second portion of the column may includecomparing the second portion of the column to profile data 441 using acomparing module 421 (in embodiments, the profile data 441 is notnecessarily part of the analytics engine 490). In embodiments, analyzingthe second portion of the column may include determining a measure ofcorrelation of the column with another column of the table of thedatabase that is persistently related to the column. Such columns may bepersistently or even permanently related. Ranges of correlation mayoccur, and data analytics may recognize an amount of correlation toprovide more efficient estimates for statistical values. In embodiments,analyzing the second portion of the column may include classifying thesecond portion of the column into a category.

The interfacing module 422 may interface with the analytics engine 490to estimate, by analyzing the second portion of the column, the firststatistical value for the column. Data from the table of the databasemay be transmitted to the analytics engine. A specific query related todata from the table of the database may be transmitted to the analyticsengine (e.g., for natural language processing which may identify one ormore concepts associated with the different elements in the specificquery and use these concepts to filter though the different data sources442 stored in a corpus 440). The specific query may be resolved by theanalytics engine (e.g., using natural language processing which mayevaluate the specific query to identify a data source 442 that maycontain an answer to the specific query). Resolving the specific querymay include comparing the corpus 440 (e.g., data store of journals,websites, magazines, reference books, textbooks that may have beeningested) and data from the table of the database. The first statisticalvalue for the column may be determined by the analytics engine inresponse to resolving the specific query.

The generating module 430 may generate the access plan to predict acharacteristic of a set of results for the database query. In generatingthe access plan, the first statistical value for the column may be used.In embodiments, cardinality may be used. Cardinality may be used as atleast a component in determining selectivity. In embodiments,selectivity may be the characteristic of the set of results for thedatabase query. The characteristic of the set of results for thedatabase query may include a number of rows. In embodiments, thedatabase query may include a join. The characteristic of the set ofresults for the database query may include an expected join countrelated to the number of rows.

Use of the modules described may reduce CPU consumption. Use of themodules described may be used when statistics are unavailable or costly.The modules described may be used when at least a portion of the dataset can be analyzed to classify data and estimate characteristics ofdata. Use of the modules described may promote efficient queryperformance.

FIG. 5 is an example system 500 for creating an access plan for adatabase query according to embodiments. Aspects of system 500 mayinclude at least a portion of previously described systems and/oraspects of method 300. A query optimization engine 510 may triggerimplementation of other aspects of system 500 through identification ofa need for results estimates 515 (e.g., what the data will ultimatelylook like). A standard statistical estimate 520 may bedetermined/calculated. The standard statistical estimate 520 may usemaintained column/row statistics 525 which can be derived from a table530 (the table 530 may have column headers (CHs) and values (Vs)). Afirst determination 535 may be made whether sufficient statistics existfor a results estimate. In response to sufficient statistics existingfor the first determination 535, a requested estimate 580 is producedand a returned estimate 585 is delivered to the query optimizationengine 510. For instance, sufficient statistics existing may include athreshold number of values such as 30, a proportion of values relativeto a number of rows being reached such as 10%, or all of the valuesbeing within a given range. The returned estimate 585 is at least basedon the requested estimate 580. In embodiments, the returned estimate 585and the requested estimate 580 are identical.

In response to sufficient statistics not existing for the firstdetermination 535, an inquiry 540 to an analytics engine 560 may beprepared. The table 530 may be used to extract data to assist inpreparing the inquiry. Query logic 545 can assist in building theinquiry 540 (e.g., question) for the analytics engine 560. Query logic545 may use a select statement such as “from [table] where [field]equals [value].” In embodiments, little or no data exists in the table530 to use in building the inquiry 540.

In response to preparing the inquiry 540, the analytics engine 560 mayanalyze factors such as column names and data values in combination withthe query logic 545. Generated-statistics 565 such as cardinalityestimates may be generated from an analytics engine reply. A seconddetermination 570 may be made whether the generated-statistics 565generated from the analytics are sufficient. In response to sufficientstatistics being generated as determined by the second determination570, a requested estimate 580 is produced (based on thegenerated-statistics 565) and a returned estimate 585 is delivered tothe query optimization engine 510. In response to insufficientstatistics being generated as determined by the second determination570, a default estimate 575 may be produced as the requested estimate580 and the returned estimate 585 which is delivered to the queryoptimization engine 510.

Aspects such as inquiry 540 and query logic 545 may be further describedutilizing specific use cases. At these points, both the data to bequeried and the logic of the query statement may be read. Questions forthe analytics engine 560 that can produce answers that would be used inthe place of maintained statistics can be prepared. For example, a callto the analytics engine 560 that produces a value for cardinality may beprepared. The first few values of a table may be read and passed alongwith a request to classify these objects or find what they have incommon. Values returned could include a noun (e.g., the objects are allU.S. states). Using that noun, it may be requested of the analyticsengine 560 to determine how many possible values exist for the noun(e.g., 50). The result returned would be the cardinality estimate forthat row.

As another example, aspects could be applied in determining aselectivity percentage. In particular, analyze an occasion where thequery statement selects WHERE <column_name>=<selection_value>FROM<table_name>. Passed to the analytics engine 560 would be a request forwhat is the percent likelihood that <column_name>would equal<selection_value>. Consider a detailed scenario where there is a tablenamed “Automobiles” with a list of every automobile in the world. Infurther detail, the table has a column named “color” and the querystatement asks Select VIN from AUTOMOBILES where COLOR=RED. Withoutstatistics the query engine would make a default guess using anarbitrary percentage. However, using the technique described, a call tothe analytics engine after building the request “What percentage of<AUTOMOBILES>in the world are the <COLOR> <RED>” would produce a moreaccurate response. In both use cases, the time spent by the analyticsengine 560 to answer the appropriate question may be less than the timelost due to not having the statistics available. These are just a coupleof examples of how the technique described can be implemented.

In the foregoing, reference is made to various embodiments. It should beunderstood, however, that this disclosure is not limited to thespecifically described embodiments. Instead, any combination of thedescribed features and elements, whether related to differentembodiments or not, is contemplated to implement and practice thisdisclosure. Many modifications and variations may be apparent to thoseof ordinary skill in the art without departing from the scope and spiritof the described embodiments. Furthermore, although embodiments of thisdisclosure may achieve advantages over other possible solutions or overthe prior art, whether or not a particular advantage is achieved by agiven embodiment is not limiting of this disclosure. Thus, the describedaspects, features, embodiments, and advantages are merely illustrativeand are not considered elements or limitations of the appended claimsexcept where explicitly recited in a claim(s).

As will be appreciated by one skilled in the art, aspects of the presentdisclosure may be embodied as a system, method, or computer programproduct. Accordingly, aspects of the present disclosure may take theform of an entirely hardware embodiment, an entirely software embodiment(including firmware, resident software, micro-code, etc.), or anembodiment combining software and hardware aspects that may allgenerally be referred to herein as a “circuit,” “module,” or “system.”Furthermore, aspects of the present disclosure may take the form of acomputer program product embodied in one or more computer readablemedium(s) having computer readable program code embodied thereon.

Any combination of one or more computer readable medium(s) may beutilized. The computer readable medium may be a computer readable signalmedium or a computer readable storage medium. A computer readablestorage medium may be, for example, but not limited to, an electronic,magnetic, optical, electromagnetic, infrared, or semiconductor system,apparatus, or device, or any suitable combination thereof. More specificexamples (a non-exhaustive list) of the computer readable storage mediumwould include the following: an electrical connection having one or morewires, a portable computer diskette, a hard disk, a random access memory(RAM), a read-only memory (ROM), an erasable programmable read-onlymemory (EPROM or Flash memory), an optical fiber, a portable compactdisc read-only memory (CD-ROM), an optical storage device, a magneticstorage device, or any suitable combination thereof. In the context ofthis disclosure, a computer readable storage medium may be any tangiblemedium that can contain, or store, a program for use by or in connectionwith an instruction execution system, apparatus, or device.

A computer readable signal medium may include a propagated data signalwith computer readable program code embodied therein, for example, inbaseband or as part of a carrier wave. Such a propagated signal may takeany of a variety of forms, including, but not limited to,electro-magnetic, optical, or any suitable combination thereof. Acomputer readable signal medium may be any computer readable medium thatis not a computer readable storage medium and that can communicate,propagate, or transport a program for use by or in connection with aninstruction execution system, apparatus, or device.

Program code embodied on a computer readable medium may be transmittedusing any appropriate medium, including but not limited to wireless,wire line, optical fiber cable, RF, etc., or any suitable combinationthereof.

Computer program code for carrying out operations for aspects of thepresent disclosure may be written in any combination of one or moreprogramming languages, including: an object oriented programminglanguage such as Java, Smalltalk, C++, or the like; and conventionalprocedural programming languages, such as the “C” programming languageor similar programming languages. The program code may execute asspecifically described herein. In addition, the program code may executeentirely on the user's computer, partly on the user's computer, as astand-alone software package, partly on the user's computer and partlyon a remote computer, or entirely on the remote computer or server. Inthe latter scenario, the remote computer may be connected to the user'scomputer through any type of network, including a local area network(LAN) or a wide area network (WAN), or the connection may be made to anexternal computer (for example, through the Internet using an InternetService Provider).

Aspects of the present disclosure have been described with reference toflowchart illustrations, block diagrams, or both, of methods,apparatuses (systems), and computer program products according toembodiments of this disclosure. It will be understood that each block ofthe flowchart illustrations or block diagrams, and combinations ofblocks in the flowchart illustrations or block diagrams, can beimplemented by computer program instructions. These computer programinstructions may be provided to a processor of a general purposecomputer, special purpose computer, or other programmable dataprocessing apparatus to produce a machine, such that the instructions,which execute via the processor of the computer or other programmabledata processing apparatus, create means for implementing the functionsor acts specified in the flowchart or block diagram block or blocks.

These computer program instructions may also be stored in a computerreadable medium that can direct a computer, other programmable dataprocessing apparatus, or other devices to function in a particularmanner, such that the instructions stored in the computer readablemedium produce an article of manufacture including instructions whichimplement the function or act specified in the flowchart or blockdiagram block or blocks.

The computer program instructions may also be loaded onto a computer,other programmable data processing apparatus, or other devices to causea series of operational steps to be performed on the computer, otherprogrammable apparatus, or other devices to produce a computerimplemented process such that the instructions which execute on thecomputer or other programmable apparatus provide processes forimplementing the functions or acts specified in the flowchart or blockdiagram block or blocks.

Embodiments according to this disclosure may be provided to end-usersthrough a cloud-computing infrastructure. Cloud computing generallyrefers to the provision of scalable computing resources as a serviceover a network. More formally, cloud computing may be defined as acomputing capability that provides an abstraction between the computingresource and its underlying technical architecture (e.g., servers,storage, networks), enabling convenient, on-demand network access to ashared pool of configurable computing resources that can be rapidlyprovisioned and released with minimal management effort or serviceprovider interaction. Thus, cloud computing allows a user to accessvirtual computing resources (e.g., storage, data, applications, and evencomplete virtualized computing systems) in “the cloud,” without regardfor the underlying physical systems (or locations of those systems) usedto provide the computing resources.

Typically, cloud-computing resources are provided to a user on apay-per-use basis, where users are charged only for the computingresources actually used (e.g., an amount of storage space used by a useror a number of virtualized systems instantiated by the user). A user canaccess any of the resources that reside in the cloud at any time, andfrom anywhere across the Internet. In context of the present disclosure,a user may access applications or related data available in the cloud.For example, the nodes used to create a stream computing application maybe virtual machines hosted by a cloud service provider. Doing so allowsa user to access this information from any computing system attached toa network connected to the cloud (e.g., the Internet).

The flowchart and block diagrams in the figures illustrate thearchitecture, functionality, and operation of possible implementationsof systems, methods, and computer program products according to variousembodiments of the present disclosure. In this regard, each block in theflowchart or block diagrams may represent a module, segment, or portionof code, which comprises one or more executable instructions forimplementing the specified logical function(s). It should also be notedthat, in some alternative implementations, the functions noted in theblock may occur out of the order noted in the figures. For example, twoblocks shown in succession may, in fact, be executed substantiallyconcurrently, or the blocks may sometimes be executed in the reverseorder, depending upon the functionality involved. It will also be notedthat each block of the block diagrams or flowchart illustration, andcombinations of blocks in the block diagrams or flowchart illustration,can be implemented by special purpose hardware-based systems thatperform the specified functions or acts, or combinations of specialpurpose hardware and computer instructions.

While the foregoing is directed to exemplary embodiments, other andfurther embodiments of the invention may be devised without departingfrom the basic scope thereof, and the scope thereof is determined by theclaims that follow.

1. A computer-implemented method of creating an access plan for adatabase query, the method comprising: identifying a first portion of acolumn of a table of a database in a select statement of the databasequery; estimating, by analyzing a second portion of the column, a firststatistical value for the column; and generating, using the firststatistical value for the column, the access plan to predict acharacteristic of a set of results for the database query.
 2. The methodof claim 1, wherein the first statistical value for the column includescardinality for the column.
 3. The method of claim 1, whereinselectivity is either the first statistical value for the column or thecharacteristic of the set of results for the database query.
 4. Themethod of claim 1, wherein analyzing the second portion of the columnincludes comparing the second portion of the column to profile data, thesecond portion of the column includes a column header, and thecharacteristic of the set of results for the database query includes anumber of rows.
 5. The method of claim 1, further comprising determininga statistics table is unsuitable for creating the access plan for thedatabase query, wherein the statistics table is unsuitable in responseto at least one of one of a group selected from an absence of thestatistics table, an absence of records in the statistics table,reaching a threshold burden of computing resources used for accessingthe statistics table, or a user choice to not utilize the statisticstable.
 6. The method of claim 1, wherein estimating, by analyzing thesecond portion of the column, the first statistical value for thecolumn, includes: transmitting, to an analytics engine, data from thetable of the database; transmitting, to the analytics engine for naturallanguage processing, a specific query related to data from the table ofthe database; resolving, by the analytics engine using natural languageprocessing, the specific query, wherein resolving the specific queryincludes comparing a corpus and data from the table of the database; anddetermining, by the analytics engine in response to resolving thespecific query, the first statistical value for the column.
 7. Themethod of claim 1, wherein analyzing the second portion of the columnincludes determining a measure of correlation of the column with anothercolumn of the table of the database that is persistently related to thecolumn.
 8. The method of claim 1, wherein the database query includes ajoin, analyzing the second portion of the column includes classifyingthe second portion of the column into a category, and the characteristicof the set of results for the database query includes an expected joincount related to the number of rows.
 9. A computer-implemented method ofcreating an access plan for a database query, the method comprising:determining a statistics table is unsuitable for creating the accessplan for the database query; identifying a first portion of a column ofa table of a database in a select statement of the database query;estimating, by analyzing a second portion of the column including acolumn header of the column, a cardinality value for the column; andgenerating, using the cardinality value for the column, the access planto predict a characteristic related to a number of rows of a set ofresults for the database query.
 10. The method of claim 9, whereinselectivity is the characteristic of the set of results for the databasequery.
 11. The method of claim 9, wherein estimating, by analyzing thesecond portion of the column, the first statistical value for thecolumn, includes: transmitting, to an analytics engine, data from thetable of the database; transmitting, to the analytics engine for naturallanguage processing, a specific query related to data from the table ofthe database; resolving, by the analytics engine using natural languageprocessing, the specific query, wherein resolving the specific queryincludes comparing a corpus and data from the table of the database; anddetermining, by the analytics engine in response to resolving thespecific query, the first statistical value for the column.
 12. Themethod of claim 9, wherein the database query includes a join, analyzingthe second portion of the column includes determining a measure ofcorrelation of the column with another column of the table of thedatabase that is permanently related to the column, and thecharacteristic related to a number of rows of the set of results for thedatabase query includes an expected join count related to the number ofrows. 13-20. (canceled)